package com.ts.office;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.ts.comm.SysNumber;
import com.ts.comm.SysString;
import org.apache.poi.hssf.record.crypto.Biff8EncryptionKey;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.crypt.Decryptor;
import org.apache.poi.poifs.crypt.EncryptionInfo;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;

/**
 * Excel工具类
 * Created by xhc on 2017-3-22.
 */
public class SysExcel {

    private static final String Excel_DefaultPassword_Error = "Default password is invalid for docId/saltData/saltHash";

    private static final String Excel_SuppliedPassword_Error = "Supplied password is invalid for docId/saltData/saltHash";

    /**
     * 获得Excel工作簿
     *
     * @param is        二进制文件流
     * @param sPassWord excel密码
     * @return Workbook
     * @throws Exception
     */
    public static Workbook getWorkbook(InputStream is, String sPassWord) throws Exception {
        Workbook wbObj = null;
        boolean bIsPwdError = false;
        BufferedInputStream binput = null;
        POIFSFileSystem poifs = null;
        EncryptionInfo ept = null;
        Decryptor d = null;
        try {
            if (!SysString.isEmpty(sPassWord)) {
                binput = new BufferedInputStream(is);
                poifs = new POIFSFileSystem(binput);
                try {
                    //excel2007 解密
                    ept = new EncryptionInfo(poifs);
                    d = Decryptor.getInstance(ept);

                    if (!d.verifyPassword(sPassWord)) {
                        bIsPwdError = true;
                    }
                    wbObj = WorkbookFactory.create(d.getDataStream(poifs));
                } catch (Exception e) {
                    if (bIsPwdError) {
                        throw new Exception("您上传的Excel文件有密码保护，请输入正确的密码！");
                    } else {
                        //excel2003 解密
                        Biff8EncryptionKey.setCurrentUserPassword(sPassWord);
                        wbObj = WorkbookFactory.create(poifs);
                    }
                }
            } else {
                wbObj = WorkbookFactory.create(is);
            }
        } catch (Exception e) {
            if ((SysString.toString(e.getLocalizedMessage()).equals(Excel_DefaultPassword_Error))
                    || (SysString.toString(e.getLocalizedMessage()).equals(Excel_SuppliedPassword_Error))) {
                throw new Exception("您上传的Excel文件有密码保护，请输入正确的密码！");
            } else {
                throw e;
            }
        }
        return wbObj;
    }

    /**
     * 获取Excel 所有sheet
     * @param is 文件流
     * @param sPassWord excel密码
     * @return sheets 集合
     * @throws Exception
     */
    public static ArrayList<String> getSheetName(InputStream is, String sPassWord) throws Exception {
        Workbook wbObj = null;
        Sheet sheetObj = null;
        int iSheetCount = 0;
        ArrayList<String> sheets = new ArrayList<String>();
        try {
            wbObj = getWorkbook(is, sPassWord);
            iSheetCount = wbObj.getNumberOfSheets();
            for (int i = 0; i < iSheetCount; i++) {
                sheetObj = wbObj.getSheetAt(i);
                if (sheetObj != null) {
                    sheets.add(sheetObj.getSheetName());
                }
            }

        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (is != null) {
                    is.close();
                }
            } catch (IOException e) {
            }
        }
        return sheets;
    }

    /**
     * 获得Excel公式解析器
     * @param wbObj
     * @return
     */
    public static FormulaEvaluator getFormulaEvaluator(Workbook wbObj)
    {
        FormulaEvaluator formualEva = null;
        if (wbObj instanceof HSSFWorkbook)
        {
            formualEva = new HSSFFormulaEvaluator((HSSFWorkbook)wbObj);
        }
        else if (wbObj instanceof XSSFWorkbook)
        {
            formualEva = new XSSFFormulaEvaluator((XSSFWorkbook)wbObj);
        }
        return formualEva;
    }

    /**导出excel为2007格式
     * */
    public static XSSFWorkbook exportXLSX(String sTtile, JSONArray headArr,JSONArray subTitleArr, JSONArray columnArr, JSONArray rowsArr
            ,  JSONArray footerArr)throws Exception{
        XSSFWorkbook wb=new XSSFWorkbook();
        XSSFRow row =null;
        XSSFCell cell=null;
        int lastRow;//最后一行

        XSSFSheet sheet = wb.createSheet();
        int colNum=columnArr.size();
        // 创建表的第一行,标题
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colNum - 1));
        row = sheet.createRow(0);
        row.setHeight((short) 500);
        cell=row.createCell(0);
        cell.setCellValue(sTtile);
        // 大标题样式
        XSSFCellStyle titleStyle = SysExcel.setSheetStyle(wb,1, HSSFColor.WHITE.index,18,true,true);
        // 左对齐方式
        XSSFCellStyle cellLeft = SysExcel.setSheetStyle(wb, 0, HSSFColor.WHITE.index, 9, false, true);
        // 居中对齐方式
        XSSFCellStyle cellCenter = SysExcel.setSheetStyle(wb, 1, HSSFColor.WHITE.index, 12, true, true);
        // 居右对齐方式
        XSSFCellStyle cellRight = SysExcel.setSheetStyle(wb, 2, HSSFColor.WHITE.index, 9, false, true);
        // 合计样式
        XSSFCellStyle sumStyle = SysExcel.setSheetStyle(wb, 2, HSSFColor.YELLOW.index, 10, true, true);

        cell.setCellStyle(titleStyle);
        sheet.createFreezePane(0, sheet.getLastRowNum() + 1);// 冻结窗口
        // 创建表的第二行,头部副标题信息
        JSONObject item=null;
        if(subTitleArr!=null && subTitleArr.size()>0){
            lastRow = sheet.getLastRowNum() + 1;

            String sBigTitle="";
            for(int i=0;i<subTitleArr.size();i++){
                sBigTitle= (String) subTitleArr.get(i);
                row = sheet.createRow(lastRow + i);
                sheet.addMergedRegion(new CellRangeAddress(lastRow + i, lastRow + i, 0, colNum - 1));
                row.setHeight((short) 350);
                cell = row.createCell(0);
                cell.setCellValue(sBigTitle);
            }
        }
        //创建多表头信息==========begin==============
        int arrayWidth = 0 ;
        for(int c=0;c<columnArr.size();c++){
            item= (JSONObject) columnArr.get(c);
            arrayWidth += item.getInteger("colspan");
        }
        int arrayHeight = headArr.size();
        boolean flag[][] = new boolean[arrayHeight][arrayWidth];

        for(int i = 0 ; i < arrayHeight; i ++){
            for(int j = 0 ; j < arrayWidth ; j++)
                flag[i][j] = true;
        }
        if(headArr!=null && !headArr.isEmpty()){
            JSONArray itemArr;
            int rowSpan=1;
            int colSpan=1;
            lastRow = sheet.getLastRowNum() + 1;
            int iRowNum=lastRow;//默认第一行为标题
            int megeLastCol=0;//合并列（最后索引）
            for(int i = 0 ; i < headArr.size() ;  i++ ){
                row=sheet.createRow(iRowNum);
                row.setHeight((short) 350);
                itemArr= (JSONArray) headArr.get(i);
                for (int j = 0; j <itemArr.size(); j++) {
                    int startFlag = 0;
                    for(int u = j ; u < arrayWidth; u++ ){
                        if(flag[i][u]){
                            startFlag = u;
                            break;
                        }
                    }

                    item= (JSONObject) itemArr.get(j);
                    if(item.getInteger("rowspan")==null){
                        rowSpan=1;
                    }else{
                        rowSpan=item.getInteger("rowspan");
                    }
                    if(item.getInteger("colspan")==null){
                        colSpan=1;
                    }else{
                        colSpan=item.getInteger("colspan");
                    }
                    if(rowSpan==1&&colSpan==1) {
                        cell = row.createCell(startFlag);
                    }else{
                        //cell= row.createCell(startFlag);
                        megeLastCol=startFlag+colSpan - 1;
                        //以下for循环解决合并列，无边框问题
                        for (int k = startFlag; k <=megeLastCol ; k++) {
                            cell=row.createCell(k);
                            cell.setCellStyle(cellCenter);
                            cell.setCellValue(item.getString("title"));
                        }
                        CellRangeAddress headerCRA = new CellRangeAddress(iRowNum,iRowNum+rowSpan-1,startFlag,megeLastCol );
                        sheet.addMergedRegion(headerCRA);
                    }
                    cell.setCellValue(item.getString("title"));
                    cell.setCellStyle(cellCenter);

                    /////
                    for(int x = i ; x < i+rowSpan ; x ++ ){
                        for(int y = j ; y < j+colSpan ; y ++ ){
                            int z = y;
                            while ( !flag[x][z] ){
                                z += 1;
                            }
                            flag[x][z] = false ;
                        }
                    }
                }
                iRowNum ++;
            }
        }
        //创建多表头信息==========end==============

        //创建表格内容
        if(rowsArr!=null && rowsArr.size()>0){
            JSONObject colItem=null;
            lastRow = sheet.getLastRowNum() + 1;
            String sValue="";
            String sField="";
            for(int i=0;i<rowsArr.size();i++){
                item= (JSONObject) rowsArr.get(i);
                row=sheet.createRow(lastRow+i);
                for(int c=0;c<columnArr.size();c++){
                    colItem= (JSONObject) columnArr.get(c);
                    sField=colItem.getString("field");
                    sValue=item.getString(sField);
                    //选择状态
                    if(sField.equalsIgnoreCase("state") &&"true".equals(sValue)){
                        sValue="√";
                    }
                    //如果列有，才取列内容
                    cell=row.createCell(c);
                    cell.setCellValue(sValue);
                    cell.setCellStyle(cellLeft);
                    //自动调整列宽
                    //sheet.setColumnWidth(c, colItem.getIntValue("width") * 36);
                }
            }
        }
        //创建合计行
        //组装合计信息
        if(columnArr!=null && !columnArr.isEmpty()){
            lastRow = sheet.getLastRowNum() + 1;
            row = sheet.createRow(lastRow);
            String sSumStr="";
            int colWidth=0;
            for(int c=0;c<columnArr.size();c++){
                cell=row.createCell(c);
                item= (JSONObject) columnArr.get(c);
                if(item.getInteger("width")==null){
                    colWidth=100;
                }else{
                    colWidth=item.getInteger("width");
                }
                sSumStr=SysString.toString(item.get("footer"));
                if(SysNumber.isNumber(sSumStr)){
                    cell.setCellValue(SysNumber.toDouble(sSumStr,2));
                    cell.setCellType(CellType.NUMERIC);
                }else{
                    cell.setCellValue(sSumStr);
                }
                cell.setCellStyle(sumStyle);
                //自动调整列宽
                sheet.setColumnWidth(c,colWidth*36);
            }
        }
        //创建footer数据
        if(footerArr!=null && !footerArr.isEmpty()){
            lastRow = sheet.getLastRowNum() + 1;
            String sFooter="";
            for(int j=0;j<footerArr.size();j++){
                sFooter= (String) footerArr.get(j);
                row = sheet.createRow(lastRow + j);
                sheet.addMergedRegion(new CellRangeAddress(lastRow + j, lastRow + j, 0, colNum - 1));
                row.setHeight((short) 350);
                cell = row.createCell(0);
                cell.setCellValue(sFooter);
            }
        }

        return wb;
    }
    /**
     * 方法说明：设置样式
     *
     * @param wb
     * @param align
     * @param color
     * @param fontSize
     * @param isBold
     * @return
     * @throw
     */
    public static XSSFCellStyle setSheetStyle(XSSFWorkbook wb, int align, short color, int fontSize, boolean isBold, boolean isBorder)
    {
        XSSFCellStyle hfStyle = wb.createCellStyle();
        if(align==1){//1 居中
            hfStyle.setAlignment(HorizontalAlignment.CENTER);// 设置水平对齐方式
        }else if(align==2){//2 居右
            hfStyle.setAlignment(HorizontalAlignment.RIGHT);// 设置水平对齐方式
        }else{//0 默认居左
            hfStyle.setAlignment(HorizontalAlignment.LEFT);// 设置水平对齐方式
        }
        hfStyle.setDataFormat((short)0);
        hfStyle.setShrinkToFit(true);
        hfStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        if (isBorder)
        {// 是否有边框
            hfStyle.setBorderBottom(BorderStyle.THIN);
            hfStyle.setBorderBottom(BorderStyle.THIN);// 设置表框粗细
            hfStyle.setBorderLeft(BorderStyle.THIN);
            hfStyle.setBorderRight(BorderStyle.THIN);
            hfStyle.setBorderTop(BorderStyle.THIN);
        }

        XSSFFont ft = wb.createFont();
        ft.setFontHeightInPoints((short)fontSize);
        if (isBold)
        {// 是否粗体
            ft.setBold(true);
        }
        hfStyle.setFont(ft);

        hfStyle.setFillForegroundColor(color);// 填充色
        hfStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return hfStyle;
    }
}
